{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import xlsxwriter\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "38\n",
      "['C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB101002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB101003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB101005.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB105002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB105003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB105004.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB201002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB201005.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB201006.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB201007.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB202010.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB203001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB203003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB204001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB205005.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB206004.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB212001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB213001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB301002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB301003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB303001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB303002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB303004.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB320001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB320002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB320003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB501001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB501002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB501003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB503002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB503003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB505001.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB505002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB505003.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB505004.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB505005.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\SZX_DAK_KTCJB506002.xlsx', 'C:\\\\Users\\\\lenovo\\\\Desktop\\\\采集表\\\\水专项科技资源信息采集表(2).xlsx']\n"
     ]
    }
   ],
   "source": [
    "#要融合的文件所在目录\n",
    "files_path = r'C:\\Users\\lenovo\\Desktop\\采集表'\n",
    "\n",
    "#获取目录下的文件名\n",
    "File_Name_List = os.walk(files_path)\n",
    "for root,dirs,source_xlsx in File_Name_List:\n",
    "    print(len(source_xlsx))\n",
    "\n",
    "#文件绝对路径\n",
    "files_dirname = []\n",
    "for files_dir in source_xlsx:\n",
    "    files_dirname.append(files_path + '\\\\' + files_dir)\n",
    "                         \n",
    "print(files_dirname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "38\n",
      "done\n"
     ]
    }
   ],
   "source": [
    "#输出excel的路径\n",
    "target_xlsx = r'C:\\Users\\lenovo\\Desktop\\merge.xlsx'\n",
    "\n",
    "numx = -1\n",
    "sheets_1 = []\n",
    "\n",
    "#创建一个[38,20]的数组，因为sheet有20个，文件有38个\n",
    "for i in range(38):\n",
    "    sheets_1.append([])\n",
    "\n",
    "    for j in range(20):\n",
    "        sheets_1[i].append(0)\n",
    "\n",
    "#读取所有文件的sheet并保存到二维数组中\n",
    "for i in files_dirname:\n",
    "    print(len(files_dirname))\n",
    "    wb = xlrd.open_workbook(i)\n",
    "    numx = numx + 1\n",
    "    numy = -1\n",
    "    for sheet in wb.sheets():\n",
    "        numy = numy + 1\n",
    "        #print(numx,numy)\n",
    "        sheets_1[numx][numy] = sheet\n",
    "            \n",
    "            \n",
    "print('done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "37 19\n"
     ]
    }
   ],
   "source": [
    "print(numx,numy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "all done\n"
     ]
    }
   ],
   "source": [
    "workbook = xlsxwriter.Workbook(target_xlsx)\n",
    "worksheet = []\n",
    "font = workbook.add_format({'font_size':14})\n",
    "\n",
    "#先循环sheet，再循环文件实现拼接，再循环行列获取数据\n",
    "for sheet_num in range(numy):\n",
    "    data = []\n",
    "    worksheet=workbook.add_worksheet()\n",
    "    for file_num in range(numx+1):\n",
    "        sheet = sheets_1[file_num][sheet_num]\n",
    "        for rownum in range(sheet.nrows):\n",
    "            data.append(sheet.row_values(rownum))\n",
    "  #每循环完一个sheet则输出一个sheet          \n",
    "    for i in range(len(data)):\n",
    "        for j in range(len(data[i])):\n",
    "            worksheet.write(i,j,data[i][j],font)\n",
    "\n",
    "workbook.close()\n",
    "print('all done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n"
     ]
    }
   ],
   "source": [
    "print(len(worksheet))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
